Use master
go
create database Student 
on
(
	name='Student',
	filename='D:\Student.mdf',
	size=5MB,
	maxsize=20MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='D:\Student_log.ldf',
	size=6MB,
	maxsize=28MB,
	filegrowth=3MB
)
go
use Student
go
create table Classinfo
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(20) unique not null,
)
insert into Classinfo(ClassName) values ('1班'),('2班'),('3班'),('4班'),
('5班'),('6班'),('7班'),('8班'),('9班'),('10班')
update ClassInfo set ClassName='11班' where ClassID=1
delete from Classinfo where ClassID=10
go

create table Student 

(
	StuID int primary key identity(1,1),
	ClassID int foreign key references Classinfo(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) default('男') check(StuSex in('男','女')),
	StuBirthday date ,
	StuPhone nvarchar(11) unique,
	StuAddress nvarchar(200),

)
insert into Student(ClassID,StuName,StuSex,StuPhone) values (1,'张一','男',12345678901),
(2,'张一','男',12345678902),(3,'张一','男',12345678903),
(4,'张一','男',12345678905),(5,'张一','男',12345678904),
(6,'张一','男',12345678906),(7,'张一','男',12345678907),
(8,'张一','男',12345678909),(9,'张一','男',12345678908),
(10,'张一','男',12345678911),(11,'张一','男',12345678912),(14,'张一','男',12345678913),
(12,'张一','男',12345678914),(13,'张一','男',12345678921),
(15,'张一','男',12345678915),(16,'张一','男',12345678920),
(18,'张一','男',12345678916),(17,'张一','男',12345678919),
(20,'张一','男',12345678917),(19,'张一','男',12345678918)

alter table Student add CreateDate datetime default(getdate())

update Student set CreateDate=getdate() 

delete from Student where ClassID=8
go

create table Course 
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int not null Default(1) check(CourseCredit>0 and CourseCredit<6),
	CourseType nvarchar(10) check(CourseType='专业课' or CourseType='公共课')

)
insert into Course (CourseName) values ('专业课'),('英语课'),
('高数课'),('思修课'),('体育课'),('职素课')

select CourseName from Course

update Course set CourseCredit=2 where CourseID=1
go

create table Score
(
	ScoreID int primary key identity(1,1),
	StuID int foreign key references Student(StuID),
	CourseID int foreign key references Course(CourseID),
	Score decimal(5,2) unique not null
)
alter table Score add constraint CK_Score_Score check(Score>=0 and Score<=100)


insert into Score (StuID,CourseID,Score) values (1,1,80),(2,3,31),
(3,3,62),(4,4,63),(5,5,64),(6,6,65),
(7,1,66),(8,2,37),(10,4,69),(11,5,70),
(12,6,71),(13,1,52),(14,2,73),(15,3,74),
(16,4,75),(17,5,66),(19,1,88),(20,2,90)

delete from Score where StuID=1
delete from Score where CourseID=1

